using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Mvc.Ajax;
using WASM.WebSite.Models;
using System.Data;
using System.Text;
using System.Data.SqlClient;

namespace WASM.WebSite.Controllers
{
	[Authorize()]
	public class SqlController : Controller
	{
		[AcceptVerbs(HttpVerbs.Get)]
		public ActionResult Query(Guid storageId)
		{
			SqlRepozitory sqlRepozitory = SqlRepozitory.Create(storageId);
			if (TestConnection(sqlRepozitory))
			{
				string[] databases = sqlRepozitory.GetTables();
				ViewData["Databases"] = new SelectList(databases);
			}

			ViewData["StorageId"] = storageId;
			return View();
		}

		[AcceptVerbs(HttpVerbs.Get)]
		public string Execute(Guid storageId, string query, string databaseName)
		{
			DataTable table = null;
			try
			{
				table = SqlRepozitory.Create(storageId).QueryExecute(query, databaseName);
			}
			catch (SqlException exp)
			{
				return "<b>" + exp.Message + "</b>";
			}

			StringBuilder sb = new StringBuilder();
			sb.Append("<table>");

			// Header
			sb.Append("<tr>");
			foreach (var col in table.Columns)
			{
				sb.AppendFormat("<th>{0}</th>", col);
			}
			sb.Append("</tr>");

			// Date
			foreach (DataRow row in table.Rows)
			{
				sb.Append("<tr>");
				foreach (DataColumn col in table.Columns)
				{
					sb.AppendFormat("<td>{0}</td>", row[col]);
				}
				sb.Append("</tr>");
			}

			sb.Append("</table>");

			return sb.ToString();
		}

		private bool TestConnection(SqlRepozitory sqlRepozitory)
		{
			string databaseName;
			string serverVersion;
			string errorMessage;

			if (sqlRepozitory.TestConnection(out databaseName, out serverVersion, out errorMessage))
			{
				ViewData["IsConnectionSuccess"] = true;
				ViewData["Database"] = databaseName;
				ViewData["ServerVersion"] = serverVersion;
				return true;
			}
			else
			{
				ViewData["IsConnectionSuccess"] = false;
				ViewData["ErrorMessage"] = errorMessage;
				return false;
			}
		}
	}
}
